package com.cym.service;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.cym.model.Database;
import com.cym.sqlHelper.bean.Sort;
import com.cym.sqlHelper.bean.Sort.Direction;
import com.cym.sqlHelper.utils.ConditionAndWrapper;
import com.cym.sqlHelper.utils.SqlHelper;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class DatabaseService {
	@Autowired
	SqlHelper sqlHelper;
	@Value("${spring.datasource.url}")
	String url;

	@Value("${spring.datasource.username}")
	String username;

	@Value("${spring.datasource.password}")
	String password;

	public List<Database> getByType(Integer type) {
		ConditionAndWrapper conditionAndWrapper = new ConditionAndWrapper();
		if (type != null) {
			conditionAndWrapper.eq(Database::getType, type);
		}

		Sort sort = new Sort(Database::getName, Direction.ASC);

		return sqlHelper.findListByQuery(conditionAndWrapper, sort, Database.class);
	}

	public String buildSql(String fromBaseId, String fromTable, String toTable, String mainKey) {
		try {
			Database fromBase = sqlHelper.findById(fromBaseId, Database.class);

			// 获取源库源表结构
			DataSource dataSource = new SimpleDataSource(url.replace("/postgres", "/" + fromBase.getName()), username, password, "org.postgresql.Driver");

			String schema = null;
			String table = null;
			if (fromTable.contains(".")) {
				schema = fromTable.split("\\.")[0];
				table = fromTable.split("\\.")[1];
			} else {
				schema = fromTable.split("_")[0] + "_" + fromTable.split("_")[1];
				table = fromTable;
			}

			// 获取字段列
			String checkSql = "SELECT column_name FROM information_schema.columns WHERE table_schema='" + schema + "' AND table_name='" + table + "'";
			List<Entity> entities = Db.use(dataSource).query(checkSql);

			String sql = "INSERT INTO ${toTable} SELECT t.* FROM ${fromTable} t ON CONFLICT (${mainKey}) DO UPDATE SET"//
					.replace("${mainKey}", mainKey)//
					.replace("${fromTable}", fromTable)//
					.replace("${toTable}", toTable)//
			;

			List<String> updates = new ArrayList<String>();
			for (Entity entity : entities) {
				String columnName = entity.getStr("column_name");
				if (columnName.equals(mainKey)) {
					continue;
				}
				updates.add("    " + columnName + " = EXCLUDED." + columnName);
			}

			String updateSql = StrUtil.join(",\n", updates);
			sql += updateSql + ";";

			return sql;

		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return e.getMessage();
		}

	}

	public String buildSqlDel(String fromTable) {

		return "TRUNCATE TABLE " + fromTable + ";";
	}

	public String buildJsonSql(String fromTable, String jsonColumn, String jsonAnalysis) {
//		if (type == 0) { // json 对象
//			String sql = """
//					WITH ${jsonColumn} AS (
//					    SELECT oly._id,
//					           replace(oly.${jsonColumn}, '''', '"')::jsonb AS ${jsonColumn}
//					    FROM ${fromTable} oly
//					    WHERE oly.${jsonColumn} <> ''
//					      AND oly.${jsonColumn} <> '{}'
//					      AND oly.${jsonColumn} LIKE '{%}'
//					      AND public.is_valid_json(replace(oly.${jsonColumn}, '''', '"'))
//					),
//					${jsonColumn}_list AS (
//					    SELECT
//					    	ss._id
//					${jsonToList}
//					    FROM ${jsonColumn} ss
//					)
//					SELECT * FROM ${jsonColumn}_list;
//										""";
//
//			String[] properties = jsonAnalysis.split("\n");
//			String jsonToList = "";
//			for (String property : properties) {
//				jsonToList += "    	," + jsonColumn + "->>'" + property + "' as " + property + "\n";
//			}
//
//			sql = sql.replace("${fromTable}", fromTable).replace("${jsonToList}", jsonToList).replace("${jsonColumn}", jsonColumn);
//
//			return sql;
//
//		} else { // 数组
		String sql = null;
		if (jsonAnalysis.contains("company_name")) {
			sql = "WITH ${jsonColumn} AS\r\n"
					+ "					  (SELECT oly._id,\r\n"
					+ "					          oly.company_id,\r\n"
					+ "					          oly.company_name,\r\n"
					+ "					          jsonb_array_elements(replace(oly.${jsonColumn}, '''', '\"')::JSONB) AS ${jsonColumn}\r\n"
					+ "					   FROM ${fromTable} oly\r\n"
					+ "					   WHERE oly.${jsonColumn} <> ''\r\n"
					+ "					     AND oly.${jsonColumn} <> '[]'\r\n"
					+ "					     AND oly.${jsonColumn} LIKE '[%]'\r\n"
					+ "					     AND public.is_valid_json(replace(oly.${jsonColumn}, '''', '\"')) ),\r\n"
					+ "					     ${jsonColumn}_list AS\r\n"
					+ "					  (SELECT\r\n"
					+ "					  		  ${jsonToList}\r\n"
					+ "					   FROM ${jsonColumn} ss)\r\n"
					+ "					SELECT ml.*,\r\n"
					+ "					       olci.credit_number\r\n"
					+ "					FROM ${jsonColumn}_list ml\r\n"
					+ "					LEFT JOIN ods_ly.ods_ly_company_info olci ON ml.company_id = olci._id ;";

		} else {
			sql = "WITH ${jsonColumn} AS\r\n"
					+ "					  (SELECT\r\n"
					+ "					          jsonb_array_elements(replace(oly.${jsonColumn}, '''', '\"')::JSONB) AS ${jsonColumn}\r\n"
					+ "					   FROM ${fromTable} oly\r\n"
					+ "					   WHERE oly.${jsonColumn} <> ''\r\n"
					+ "					     AND oly.${jsonColumn} <> '[]'\r\n"
					+ "					     AND oly.${jsonColumn} LIKE '[%]'\r\n"
					+ "					     AND public.is_valid_json(replace(oly.${jsonColumn}, '''', '\"')) ),\r\n"
					+ "					     ${jsonColumn}_list AS\r\n"
					+ "					  (SELECT\r\n"
					+ "					  		  ${jsonToList}\r\n"
					+ "					   FROM ${jsonColumn} ss)\r\n"
					+ "					SELECT ml.*\r\n"
					+ "					FROM ${jsonColumn}_list ml";
		}

		String[] properties = jsonAnalysis.split("\n");
		String jsonToList = "";
		boolean hasFirst = false;
		for (String property : properties) {
			if (StrUtil.isNotBlank(property) && !property.equals("_id")) {
				if (!hasFirst) {
					jsonToList += "		" + jsonColumn + "->>'" + property + "' as " + property + "\n";
					hasFirst = true;
				} else {
					jsonToList += "		," + jsonColumn + "->>'" + property + "' as " + property + "\n";
				}
			}
		}

		jsonToList = jsonToList.substring(1);

		sql = sql.replace("${jsonToList}", jsonToList).replace("${fromTable}", fromTable).replace("${jsonColumn}", jsonColumn);

		return sql;
//		}
	}

	public String buildJsonSqlInsert(String tmpTable, String toTable, String jsonAnalysis) {
		String sql = "INSERT INTO ${toTable}\r\n"
				+ "				SELECT DISTINCT ON (_id) t.*\r\n"
				+ "				FROM ${tmpTable}  t\r\n"
				+ "				ON CONFLICT (_id) DO UPDATE\r\n"
				+ "				SET (${analysis})=\r\n"
				+ "				ROW(${analysisExcluded})";

		String[] properties = jsonAnalysis.trim().split("\n");
		String analysis = "";
		String analysisExcluded = "";
		boolean hasFirst = false;
		for (String property : properties) {
			if (StrUtil.isNotBlank(property) && !property.equals("_id")) {
				property = property.replace(",", "");
				if (!hasFirst) {
					analysis += "," + property.trim();
					analysisExcluded += "EXCLUDED." + property.trim();
					hasFirst = true;
				} else {
					analysis += property.trim();
					analysisExcluded += ",EXCLUDED." + property.trim();
				}
			}
		}

		sql = sql.replace("${tmpTable}", tmpTable).replace("${toTable}", toTable).replace("${analysisExcluded}", analysisExcluded).replace("${analysis}", analysis);

		return sql;
	}

	public String buildJsonSqlCreate(String toTable, String jsonAnalysis) {
		String tableName = toTable.split("\\.")[1];

		String sql = "CREATE TABLE ${toTable} (\r\n"
				+ "				${analysis}\r\n"
				+ "				) distribute by shard(_id) to group default_group;\r\n"
				+ "\r\n"
				+ "				ALTER TABLE ${toTable} ADD CONSTRAINT unique_${tableName}_id UNIQUE (_id);\r\n"
				+ "\r\n"
				+ "				CREATE TABLE ${toTable}_tmp (\r\n"
				+ "				${analysis}\r\n"
				+ "				) distribute by shard(_id) to group default_group;\r\n"
				+ "\r\n"
				+ "				ALTER TABLE ${toTable}_tmp ADD CONSTRAINT unique_${tableName}_tmp_id UNIQUE (_id);";

		String[] properties = jsonAnalysis.split("\n");
		String analysis = "";
		for (int i = 0; i < properties.length; i++) {
			if (i == 0) {
				analysis += "	" + properties[i].trim() + " varchar(255)  DEFAULT NULL\n";
			} else {
				analysis += "	," + properties[i].trim() + " varchar(255)  DEFAULT NULL\n";
			}
		}

		sql = sql.replace("${analysis}", analysis).replace("${toTable}", toTable).replace("${tableName}", tableName);

		return sql;
	}

}
